package kr.hkit.wordnote.db;

import java.util.ArrayList;

import kr.hkit.wordnote.exception.DBException;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.Toast;

public class WordDBHelper extends SQLiteOpenHelper {
	Context mContext;
	public WordDBHelper(Context context) {
		super(context, "Word.db", null, 1);
		mContext = context;
		
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE wordtest ( _id INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "engword TEXT, korword TEXT, date DATE);");
		db.execSQL("INSERT INTO wordtest VALUES(null, 'a', '에이', null);");
		db.execSQL("INSERT INTO wordtest VALUES(null, 'b', '비', null);");
		db.execSQL("INSERT INTO wordtest VALUES(null, 'c', '시', null);");
		db.execSQL("INSERT INTO wordtest VALUES(null, 'd', '디', null);");
		db.execSQL("INSERT INTO wordtest VALUES(null, 'e', '이', null);");
		db.execSQL("INSERT INTO wordtest VALUES(null, 'f', '에프', null);");
		db.execSQL("INSERT INTO wordtest VALUES(null, 'g', '지', null);");
		db.execSQL("INSERT INTO wordtest VALUES(null, 'h', '에이치', null);");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS wordtest");
		onCreate(db);
	}
	
	public ArrayList<WordDTO> selectWord() throws DBException{
		ArrayList<WordDTO> arList = new ArrayList<>();
		SQLiteDatabase db = null;
		Cursor cursor = null;
		try {
			db = this.getReadableDatabase();
			//기본 정렬은 가장 나중에 입력된 단어가 맨 위에 나오도록 한다.
			cursor = db.rawQuery("SELECT engword, korword FROM wordtest ORDER BY _id desc", null);

			while (cursor.moveToNext()) {
				WordDTO dto = new WordDTO();
				dto.setEng(cursor.getString(0));
				dto.setKor(cursor.getString(1));
				arList.add(dto);
			}
		} catch (Exception e) {
			throw new DBException("DB에서 정보를 가져오지 못했습니다.");
		} finally {
			cursor.close();
			db.close();
		}
		return arList;
	}
	
	public void insertWord(String eng, String kor) throws DBException{
		SQLiteDatabase db = null;
		try{
			db = this.getReadableDatabase();
			ContentValues row = new ContentValues();
			row.put("engword", eng);
			row.put("korword", kor);
			db.insert("wordtest", null, row);
		}catch(Exception e){
			throw new DBException("DB에 정보를 저장하지 못했습니다");
		} finally {
			db.close();
		}
	}
	
	public void updateWord(String eng, String kor)throws DBException{
		SQLiteDatabase db = null;
		try{
			db = this.getReadableDatabase();
			db.execSQL("UPDATE wordtest SET korword='"+kor+"' WHERE engword='"+eng+"';");
		} catch(Exception e) {
			throw new DBException("DB에 정보를 저장하지 못했습니다");
		} finally {
			db.close();
		}
	}
	
	public void deleteWord(String eng) throws DBException{
		SQLiteDatabase db = null;
		try{
			db = this.getReadableDatabase();
			db.execSQL("DELETE FROM wordtest WHERE engword='"+eng+"';");
		} catch(Exception e) {
			throw new DBException("단어 정보를 삭제하지 못했습니다");
		} finally {
			db.close();
		}
	}
}
